<?
session_start();
include("classes/connect.php");
require_once('classes/class.jira.php');
include('config.php');
include 'paginate/libs/ps_pagination.php';
ini_set('display_errors', 'Off');

$post = $_GET['post'];
$assignee = $_GET['assignee'];
$status=$_GET['urlstatus'];
$status=html_entity_decode($status);

$search=html_entity_decode($search);
$priority=$_GET['priority'];

function get_time_ago($time_stamp)
{
    $time_difference = strtotime('now') - $time_stamp;

    if ($time_difference >= 60 * 60 * 24 * 365.242199)
    {
        /*
         * 60 seconds/minute * 60 minutes/hour * 24 hours/day * 365.242199 days/year
         * This means that the time difference is 1 year or more
         */
        return get_time_ago_string($time_stamp, 60 * 60 * 24 * 365.242199, 'year');
    }
    elseif ($time_difference >= 60 * 60 * 24 * 30.4368499)
    {
        /*
         * 60 seconds/minute * 60 minutes/hour * 24 hours/day * 30.4368499 days/month
         * This means that the time difference is 1 month or more
         */
        return get_time_ago_string($time_stamp, 60 * 60 * 24 * 30.4368499, 'month');
    }
    elseif ($time_difference >= 60 * 60 * 24 * 7)
    {
        /*
         * 60 seconds/minute * 60 minutes/hour * 24 hours/day * 7 days/week
         * This means that the time difference is 1 week or more
         */
        return get_time_ago_string($time_stamp, 60 * 60 * 24 * 7, 'week');
    }
    elseif ($time_difference >= 60 * 60 * 24)
    {
        /*
         * 60 seconds/minute * 60 minutes/hour * 24 hours/day
         * This means that the time difference is 1 day or more
         */
        return get_time_ago_string($time_stamp, 60 * 60 * 24, 'day');
    }
    elseif ($time_difference >= 60 * 60)
    {
        /*
         * 60 seconds/minute * 60 minutes/hour
         * This means that the time difference is 1 hour or more
         */
        return get_time_ago_string($time_stamp, 60 * 60, 'hour');
    }
    else
    {
        /*
         * 60 seconds/minute
         * This means that the time difference is a matter of minutes
         */
        return get_time_ago_string($time_stamp, 60, 'minute');
    }
}

function get_time_ago_string($time_stamp, $divisor, $time_unit)
{
    $time_difference = strtotime("now") - $time_stamp;
    $time_units      = floor($time_difference / $divisor);

    settype($time_units, 'string');

    if ($time_units === '0')
    {
        return 'less than 1 ' . $time_unit . ' ago';
    }
    elseif ($time_units === '1')
    {
        return '1 ' . $time_unit . ' ago';
    }
    else
    {
        /*
         * More than "1" $time_unit. This is the "plural" message.
         */
        // TODO: This pluralizes the time unit, which is done by adding "s" at the end; this will not work for i18n!
        return $time_units . ' ' . $time_unit . 's ago';
    }
}
if(isset($_GET['from']) && isset($_GET['to']))
{
    $from=$_GET['from'];
    $to=$_GET['to'];
    //echo $from;
     if($status===$action_OpenCV || $status===$$action_Rejected)
     {
        if($priority==""){
                if($assignee!=""){//assignee has value
                        if($post!=""){//post has value
                                $sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2  from trn_emaildetails a  WHERE a.currentStatus='".$status."' AND a.post='".$post."' AND a.currentAssignee='".$assignee."' AND a.deleted='0' AND date( FROM_UNIXTIME( a.modifiedDate ) ) >=' ".$from."' AND date( FROM_UNIXTIME( a.modifiedDate ) ) <= '".$to."'  ORDER BY a.modifiedDate DESC";
                        }
                        else{//post all
                                $sql="SELECT a. *,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2  from trn_emaildetails a   WHERE a.currentStatus='".$status."' AND a.currentAssignee='".$assignee."' AND a.deleted='0'  AND date( FROM_UNIXTIME( a.modifiedDate ) ) >= '".$from."' AND date( FROM_UNIXTIME( a.modifiedDate ) ) <= '".$to."'   ORDER BY a.modifiedDate DESC";
                        }
                }
                else{
                        if($post!=""){//post has value
                                $sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2  from trn_emaildetails a  WHERE a.currentStatus='".$status."' AND a.post='".$post."' AND a.deleted='0' AND date( FROM_UNIXTIME( a.modifiedDate ) ) >= '".$from."' AND date( FROM_UNIXTIME( a.modifiedDate ) ) <= '".$to."'    ORDER BY a.modifiedDate DESC";
                        }
                        else{//post all
                                $sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2  from trn_emaildetails a  WHERE a.currentStatus='".$status."' AND a.deleted='0'  AND date( FROM_UNIXTIME( a.modifiedDate ) ) >= '".$from."' AND date( FROM_UNIXTIME( a.modifiedDate ) ) <= '".$to."'   ORDER BY a.modifiedDate DESC";
                        }
                }
        }
        else{
            if($assignee!=""){//assignee has value
                    if($post!=""){//post has value
                            $sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2  from trn_emaildetails a    WHERE a.currentStatus='".$status."' AND a.post='".$post."' AND a.priority='".$priority."' AND a.currentAssignee='".$assignee."' AND a.deleted='0' AND date( FROM_UNIXTIME( a.modifiedDate ) ) >= '".$from."' AND date( FROM_UNIXTIME( a.modifiedDate ) ) <= '".$to."'    ORDER BY a.modifiedDate DESC";

                    }
                    else{//post all
                            $sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2  from trn_emaildetails a  WHERE a.currentStatus='".$status."' AND a.currentAssignee='".$assignee."' AND a.priority='".$priority."' AND deleted='0'  AND date( FROM_UNIXTIME( a.modifiedDate ) ) >= '".$from."' AND date( FROM_UNIXTIME( a.modifiedDate ) ) <= '".$to."'   ORDER BY a.modifiedDate DESC";
                    }
            }
            else{
		if($post!=""){//post has value
			$sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2  from trn_emaildetails a  WHERE a.currentStatus='".$status."' AND a.post='".$post."' AND a.deleted='0' AND a.priority='".$priority."'  AND date( FROM_UNIXTIME( a.modifiedDate ) ) >= '".$from."' AND date( FROM_UNIXTIME( a.modifiedDate ) ) <= '".$to."'   ORDER BY a.modifiedDate DESC";
		}
		else{//post all
			$sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2  from trn_emaildetails a  WHERE a.currentStatus='".$status."' AND a.deleted='0' AND priority='".$priority."'  AND date( FROM_UNIXTIME( a.modifiedDate ) ) >= '".$from."' AND date( FROM_UNIXTIME( a.modifiedDate ) ) <= '".$to."'   ORDER BY a.modifiedDate DESC";
		}
	}
        }
     }
    else {
         if($priority==""){
                if($assignee!=""){//assignee has value
                        if($post!=""){//post has value
                                $sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2  ,b.postname from trn_emaildetails a INNER JOIN sys_jobposts b ON b.code=a.post  WHERE a.currentStatus='".$status."' AND a.post='".$post."' AND a.currentAssignee='".$assignee."' AND a.deleted='0' AND date( FROM_UNIXTIME( a.modifiedDate ) ) >=' ".$from."' AND date( FROM_UNIXTIME( a.modifiedDate ) ) <= '".$to."'  ORDER BY a.modifiedDate DESC";
                        }
                        else{//post all
                                $sql="SELECT a. *,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2   ,b.postname from trn_emaildetails a INNER JOIN sys_jobposts b ON b.code=a.post   WHERE a.currentStatus='".$status."' AND a.currentAssignee='".$assignee."' AND a.deleted='0'  AND date( FROM_UNIXTIME( a.modifiedDate ) ) >= '".$from."' AND date( FROM_UNIXTIME( a.modifiedDate ) ) <= '".$to."'   ORDER BY a.modifiedDate DESC";
                        }
                }
                else{
                        if($post!=""){//post has value
                                $sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2   ,b.postname from trn_emaildetails a INNER JOIN sys_jobposts b ON b.code=a.post  WHERE a.currentStatus='".$status."' AND a.post='".$post."' AND a.deleted='0' AND date( FROM_UNIXTIME( a.modifiedDate ) ) >= '".$from."' AND date( FROM_UNIXTIME( a.modifiedDate ) ) <= '".$to."'    ORDER BY a.modifiedDate DESC";
                        }
                        else{//post all
                                $sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2   ,b.postname from trn_emaildetails a INNER JOIN sys_jobposts b ON b.code=a.post  WHERE a.currentStatus='".$status."' AND a.deleted='0'  AND date( FROM_UNIXTIME( a.modifiedDate ) ) >= '".$from."' AND date( FROM_UNIXTIME( a.modifiedDate ) ) <= '".$to."'   ORDER BY a.modifiedDate DESC";
                        }
                }
        }
        else{
            if($assignee!=""){//assignee has value
                    if($post!=""){//post has value
                            $sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2   ,b.postname from trn_emaildetails a INNER JOIN sys_jobposts b ON b.code=a.post   WHERE a.currentStatus='".$status."' AND a.post='".$post."' AND a.priority='".$priority."' AND a.currentAssignee='".$assignee."' AND a.deleted='0' AND date( FROM_UNIXTIME( a.modifiedDate ) ) >= '".$from."' AND date( FROM_UNIXTIME( a.modifiedDate ) ) <= '".$to."'    ORDER BY a.modifiedDate DESC";

                    }
                    else{//post all
                            $sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2   ,b.postname from trn_emaildetails a INNER JOIN sys_jobposts b ON b.code=a.post  WHERE a.currentStatus='".$status."' AND a.currentAssignee='".$assignee."' AND a.priority='".$priority."' AND deleted='0'  AND date( FROM_UNIXTIME( a.modifiedDate ) ) >= '".$from."' AND date( FROM_UNIXTIME( a.modifiedDate ) ) <= '".$to."'   ORDER BY a.modifiedDate DESC";
                    }
            }
            else{
		if($post!=""){//post has value
			$sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2   ,b.postname from trn_emaildetails a INNER JOIN sys_jobposts b ON b.code=a.post WHERE a.currentStatus='".$status."' AND a.post='".$post."' AND a.deleted='0' AND a.priority='".$priority."'  AND date( FROM_UNIXTIME( a.modifiedDate ) ) >= '".$from."' AND date( FROM_UNIXTIME( a.modifiedDate ) ) <= '".$to."'   ORDER BY a.modifiedDate DESC";
		}
		else{//post all
			$sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2   ,b.postname from trn_emaildetails a INNER JOIN sys_jobposts b ON b.code=a.post WHERE a.currentStatus='".$status."' AND a.deleted='0' AND priority='".$priority."'  AND date( FROM_UNIXTIME( a.modifiedDate ) ) >= '".$from."' AND date( FROM_UNIXTIME( a.modifiedDate ) ) <= '".$to."'   ORDER BY a.modifiedDate DESC";
		}
	}
        }
     }
}
 else {
     if($status===$action_OpenCV || $status===$$action_Rejected)
     {                     
        if($priority==""){
                if($assignee!=""){//assignee has value
                        if($post!=""){//post has value
                                $sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2  from trn_emaildetails a  WHERE a.currentStatus='".$status."' AND a.post='".$post."' AND a.currentAssignee='".$assignee."' AND a.deleted='0'  ORDER BY a.modifiedDate DESC";
                        }
                        else{//post all
                                $sql="SELECT a. *,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2  from trn_emaildetails a   WHERE a.currentStatus='".$status."' AND a.currentAssignee='".$assignee."' AND a.deleted='0'  ORDER BY a.modifiedDate DESC";
                        }
                }
                else{
                        if($post!=""){//post has value
                                $sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2  from trn_emaildetails a    WHERE a.currentStatus='".$status."' AND a.post='".$post."' AND a.deleted='0'  ORDER BY a.modifiedDate DESC";
                        }
                        else{//post all
                                $sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2  from trn_emaildetails a   WHERE a.currentStatus='".$status."' AND a.deleted='0'  ORDER BY a.modifiedDate DESC";
                   
                        }
                }
        }
        else{
            if($assignee!=""){//assignee has value
                    if($post!=""){//post has value
                            $sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2  from trn_emaildetails a  WHERE a.currentStatus='".$status."' AND a.post='".$post."' AND a.priority='".$priority."' AND a.currentAssignee='".$assignee."' AND a.deleted='0'  ORDER BY a.modifiedDate DESC";

                    }
                    else{//post all
                            $sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2  from trn_emaildetails a  WHERE a.currentStatus='".$status."' AND a.currentAssignee='".$assignee."' AND a.priority='".$priority."' AND deleted='0'  ORDER BY a.modifiedDate DESC";
                    }
            }
            else{
                    if($post!=""){//post has value
                            $sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2  from trn_emaildetails a WHERE a.currentStatus='".$status."' AND a.post='".$post."' AND a.deleted='0' AND a.priority='".$priority."'  ORDER BY a.modifiedDate DESC";
                    }
                    else{//post all
                            $sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2  from trn_emaildetails a  WHERE a.currentStatus='".$status."' AND a.deleted='0' AND priority='".$priority."'  ORDER BY a.modifiedDate DESC";
                    }
            }
    }
     }
    else {
            if($priority==""){
                if($assignee!=""){//assignee has value
                        if($post!=""){//post has value
                                $sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2  ,b.postname from trn_emaildetails a INNER JOIN sys_jobposts b ON b.code=a.post WHERE a.currentStatus='".$status."' AND a.post='".$post."' AND a.currentAssignee='".$assignee."' AND a.deleted='0'  ORDER BY a.modifiedDate DESC";
                        }
                        else{//post all
                                $sql="SELECT a. *,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2  ,b.postname from trn_emaildetails a INNER JOIN sys_jobposts b ON b.code=a.post   WHERE a.currentStatus='".$status."' AND a.currentAssignee='".$assignee."' AND a.deleted='0'  ORDER BY a.modifiedDate DESC";
                        }
                }
                else{
                        if($post!=""){//post has value
                                $sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2 ,b.postname from trn_emaildetails a INNER JOIN sys_jobposts b ON b.code=a.post    WHERE a.currentStatus='".$status."' AND a.post='".$post."' AND a.deleted='0'  ORDER BY a.modifiedDate DESC";
                        }
                        else{//post all
                                $sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2  ,b.postname from trn_emaildetails a INNER JOIN sys_jobposts b ON b.code=a.post   WHERE a.currentStatus='".$status."' AND a.deleted='0'  ORDER BY a.modifiedDate DESC";
                   
                        }
                }
        }
            else{
            if($assignee!=""){//assignee has value
                    if($post!=""){//post has value
                            $sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2  ,b.postname from trn_emaildetails a INNER JOIN sys_jobposts b ON b.code=a.post  WHERE a.currentStatus='".$status."' AND a.post='".$post."' AND a.priority='".$priority."' AND a.currentAssignee='".$assignee."' AND a.deleted='0'  ORDER BY a.modifiedDate DESC";

                    }
                    else{//post all
                            $sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2  ,b.postname from trn_emaildetails a INNER JOIN sys_jobposts b ON b.code=a.post  WHERE a.currentStatus='".$status."' AND a.currentAssignee='".$assignee."' AND a.priority='".$priority."' AND deleted='0'  ORDER BY a.modifiedDate DESC";
                    }
            }
            else{
                    if($post!=""){//post has value
                            $sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2  ,b.postname from trn_emaildetails a INNER JOIN sys_jobposts b ON b.code=a.post WHERE a.currentStatus='".$status."' AND a.post='".$post."' AND a.deleted='0' AND a.priority='".$priority."'  ORDER BY a.modifiedDate DESC";
                    }
                    else{//post all
                            $sql="SELECT a.*,concat(FROM_UNIXTIME(a.dateReceived)) as rec, concat( FROM_UNIXTIME( a.modifiedDate ) ) AS rec2  ,b.postname from trn_emaildetails a INNER JOIN sys_jobposts b ON b.code=a.post  WHERE a.currentStatus='".$status."' AND a.deleted='0' AND priority='".$priority."'  ORDER BY a.modifiedDate DESC";
                    }
            }
    }
     }
 }


$query=mysql_query($sql);
//$pager = new PS_Pagination( $conn, $sql, 20, 20, null );
//$query= $pager->paginate(); 
$num = mysql_num_rows($query);


	$config->username= $username;
	$config->password= $pass;
	$config->port= "";
	$config->host= $host;
	
	$queryIssue = new Jira($config);
	$array=$queryIssue->queryPermission($project);
	$response = json_decode($array->responseBody);
        $arr_out = array();
		if($response->permissions->DELETE_ISSUE->havePermission==1){
			$admin=1;
		}
		else{
			$admin=0;
		}
if($num >= 1 ){    
    //looping through the records retrieved
//echo "<form action='deletedata.php' method='post'>";
    if($status===$action_OpenCV || $status===$$action_Rejected)
     {
        while($row = mysql_fetch_array($query)){
        $date1 = $row['dateReceived'];
        $date2 = time();
        $subTime = $date1 - $date2;
        $ye = ($subTime/(60*60*24*365));
        $da = ($subTime/(60*60*24))%365;
        $ho = ($subTime/(60*60))%24;
        $mi = ($subTime/60)%60;
        $ela=$da." Days";
        $types = explode("-", $ela);
       /// $ela=$types[1].' '.$types[2].' '.$types[3];
        $check = explode("-", $da);
        $day=$check[1];
        
        $ela=$types[1];
        
        
        if($day<1)
        {
        	$ela='Today';
        }
          $arr=  array(
                    "Id"=> $i + 1,
                    "rwid" => $row['id'],
                    "name" => $row['applicantName'],
                    "email" =>$row['email'],
                    "post" =>$row['post'],
                    "subject" =>$row['subject'],
                    "ReceivedDate" => $row['rec'],
                    "ModifiedDate" => $row['rec2'],
                    "modifyby" => $row['modifiedBy'],
                    "priority" => $row['priority'],
                    "currentass" => $row['currentAssignee'],
                    "currentassName" => $row['currentAssigneeName'],
                    "jiraid" => $row['jiraTaskID'],
                    "vcode" => $row['vcode'],
                    "cus" => $row['currentStatus'],
                    "elapsed" => $ela
                   );
                $arr_out[] = $arr;
                $i++;
        }     
    
     }
    else {
          while($row = mysql_fetch_array($query)){
        $date1 = $row['dateReceived'];
        $date2 = time();
        $subTime = $date1 - $date2;
        $ye = ($subTime/(60*60*24*365));
        $da = ($subTime/(60*60*24))%365;
        $ho = ($subTime/(60*60))%24;
        $mi = ($subTime/60)%60;
        $ela=$da." Days";
        $types = explode("-", $ela);
       /// $ela=$types[1].' '.$types[2].' '.$types[3];
        $ela=$types[1];
          $arr=  array(
                    "Id"=> $i + 1,
                    "rwid" => $row['id'],
                    "name" => $row['applicantName'],
                    "email" =>$row['email'],
                    "post" =>$row['postname'],
                    "subject" =>$row['subject'],
                    "ReceivedDate" => $row['rec'],
                    "ModifiedDate" => $row['rec2'],
                    "modifyby" => $row['modifiedBy'],
                    "priority" => $row['priority'],
                    "currentass" => $row['currentAssignee'],
                    "currentassName" => $row['currentAssigneeName'],
                    "jiraid" => $row['jiraTaskID'],
                    "vcode" => $row['vcode'],
                    "cus" => $row['currentStatus'],
                    "elapsed" => $ela
                   );
                $arr_out[] = $arr;
                $i++;
        }     
    
     }
     echo json_encode($arr_out);
//    echo "</table>";
}else{
    //if no records found
    echo "No records found!";
}
	
?>

